package com.cms.service;

import java.util.ArrayList;
import java.util.List;

import com.cms.model.ImportChannelData;
import com.cms.model.ImportCpData;
import com.cms.model.ImportData;
import com.cms.sync.model.KpaySyncLog;
import com.model.Admin;
import com.service.AbstractDateService;
import com.utils.dbcp.ConnectionManager;
import com.utils.dbcp.ModelGenerator;

public class ImportDataServiceImpl extends AbstractDateService implements
		ImportDataService {

	@Override
	public List<ImportData> getImportDataByContent(ImportData model) {
		String sql = "select * from t_importdata where day='"+model.getDay()+"' and cpid='"+model.getCpid()+"' " +
				" and appnumber='"+model.getAppnumber()+"' and appversionnumber='"+model.getAppversionnumber()+"' and channelid='"+model.getChannelid()+"' " +
				" and goodsname='"+model.getGoodsname()+"' and operator='"+model.getOperator()+"' " +
				" and source='"+model.getSource()+"' and province='"+model.getProvince()+"'";
		ModelGenerator<ImportData> dbcpmg = new ModelGenerator<ImportData>(ImportData.class);
		return dbcpmg.generateList(base, sql);
	}

	@Override
	public boolean saveImportData(List<ImportData> list) {
		String [] sql = new String[list.size()];
		ImportData model = null;
		for(int i=0;i<list.size();i++)
		{
			model = list.get(i);
			List<ImportData>  l = getImportDataByContent(model);
			if(l!=null && l.size()>0)
			{
				sql[i] = "update t_importdata set datafileid='"+model.getDatafileid()+"'," +
						"fee='"+model.getFee()+"',newusers='"
						+model.getNewusers()+"',activeuser='"
						+model.getActiveuser()+"',payingcustomers='"
						+model.getPayingcustomers()+"',paidnumber='"+model.getPaidnumber()+"' where day='"+model.getDay()+"' and cpid='"+model.getCpid()+"' " +
						" and appnumber='"+model.getAppnumber()+"' and appversionnumber='"+model.getAppversionnumber()+"' and channelid='"+model.getChannelid()+"' " +
						" and goodsname='"+model.getGoodsname()+"' and operator='"+model.getOperator()+"' " +
						" and source='"+model.getSource()+"' and province='"+model.getProvince()+"'";
			}else
			{
				model.setId(getTableId("t_importdata"));
				sql[i] = "insert into t_importdata (id,datafileid,day,cpid,appnumber,appversionnumber,channelid," +
						"goodsname,operator,source,province,fee,newusers,activeuser,payingcustomers,paidnumber) values ('"+model.getId()+"'," +
						"'"+model.getDatafileid()+"','"+model.getDay()+"','"+model.getCpid()+"'" +
						",'"+model.getAppnumber()+"','"+model.getAppversionnumber()+"','"+model.getChannelid()+"','"+
						model.getGoodsname()+"','"+model.getOperator()+"','"+
						model.getSource()+"','"+model.getProvince()+"','"+model.getFee()+
						"','"+model.getNewusers()+"','"+model.getActiveuser()+"','"+model.getPayingcustomers()+"','"+model.getPaidnumber()+"')";
			}
		}
		return ConnectionManager.getInstance().executeBatch(base, sql);
	}

	@Override
	public List<ImportChannelData> getImportChannelDataByContent(
			ImportChannelData model) {
		String sql = "select * from t_importchanneldata where day='"+model.getDay()+"' and cpid='"+model.getCpid()+"' " +
		" and appnumber='"+model.getAppnumber()+"' and appversionnumber='"+model.getAppversionnumber()+"' and channelid='"+model.getChannelid()+"' " +
		" and goodsname='"+model.getGoodsname()+"' and operator='"+model.getOperator()+"' " +
		" and source='"+model.getSource()+"' and province='"+model.getProvince()+"'";
		ModelGenerator<ImportChannelData> dbcpmg = new ModelGenerator<ImportChannelData>(ImportChannelData.class);
		return dbcpmg.generateList(base, sql);
	}

	@Override
	public List<ImportCpData> getImportCpDataByContent(ImportCpData model) {
		String sql = "select * from t_importcpdata where day='"+model.getDay()+"' and cpid='"+model.getCpid()+"' " +
		" and appnumber='"+model.getAppnumber()+"' and appversionnumber='"+model.getAppversionnumber()+"' and channelid='"+model.getChannelid()+"' " +
		" and goodsname='"+model.getGoodsname()+"' and operator='"+model.getOperator()+"' " +
		" and source='"+model.getSource()+"' and province='"+model.getProvince()+"'";
		ModelGenerator<ImportCpData> dbcpmg = new ModelGenerator<ImportCpData>(ImportCpData.class);
		return dbcpmg.generateList(base, sql);
	}

	@Override
	public boolean saveImportChannelData(List<ImportChannelData> list) {
		String [] sql = new String[list.size()];
		ImportChannelData model = null;
		for(int i=0;i<list.size();i++)
		{
			model = list.get(i);
			List<ImportChannelData>  l = getImportChannelDataByContent(model);
			if(l!=null && l.size()>0)
			{
				sql[i] = "update t_importchanneldata set datafileid='"+model.getDatafileid()+"'," +
						"fee='"+model.getFee()+"',newusers='"
						+model.getNewusers()+"',activeuser='"
						+model.getActiveuser()+"',payingcustomers='"
						+model.getPayingcustomers()+"',paidnumber='"+model.getPaidnumber()+"' where day='"+model.getDay()+"' and cpid='"+model.getCpid()+"' " +
						" and appnumber='"+model.getAppnumber()+"' and appversionnumber='"+model.getAppversionnumber()+"' and channelid='"+model.getChannelid()+"' " +
						" and goodsname='"+model.getGoodsname()+"' and operator='"+model.getOperator()+"' " +
						" and source='"+model.getSource()+"' and province='"+model.getProvince()+"'";
			}else
			{
				model.setId(getTableId("t_importchanneldata"));
				sql[i] = "insert into t_importchanneldata (id,datafileid,day,cpid,appnumber,appversionnumber,channelid," +
						"goodsname,operator,source,province,fee,newusers,activeuser,payingcustomers,paidnumber) values ('"+model.getId()+"'," +
						"'"+model.getDatafileid()+"','"+model.getDay()+"','"+model.getCpid()+"'" +
						",'"+model.getAppnumber()+"','"+model.getAppversionnumber()+"','"+model.getChannelid()+"','"+
						model.getGoodsname()+"','"+model.getOperator()+"','"+
						model.getSource()+"','"+model.getProvince()+"','"+model.getFee()+
						"','"+model.getNewusers()+"','"+model.getActiveuser()+"','"+model.getPayingcustomers()+"','"+model.getPaidnumber()+"')";
			}
		}
		return ConnectionManager.getInstance().executeBatch(base, sql);
	}

	@Override
	public boolean saveImportCpData(List<ImportCpData> list) {
		String [] sql = new String[list.size()];
		ImportCpData model = null;
		for(int i=0;i<list.size();i++)
		{
			model = list.get(i);
			List<ImportCpData>  l = getImportCpDataByContent(model);
			if(l!=null && l.size()>0)
			{
				sql[i] = "update t_importcpdata set datafileid='"+model.getDatafileid()+"'," +
						"fee='"+model.getFee()+"',newusers='"
						+model.getNewusers()+"',activeuser='"
						+model.getActiveuser()+"',payingcustomers='"
						+model.getPayingcustomers()+"',paidnumber='"+model.getPaidnumber()+"' where day='"+model.getDay()+"' and cpid='"+model.getCpid()+"' " +
						" and appnumber='"+model.getAppnumber()+"' and appversionnumber='"+model.getAppversionnumber()+"' and channelid='"+model.getChannelid()+"' " +
						" and goodsname='"+model.getGoodsname()+"' and operator='"+model.getOperator()+"' " +
						" and source='"+model.getSource()+"' and province='"+model.getProvince()+"'";
			}else
			{
				model.setId(getTableId("t_importcpdata"));
				sql[i] = "insert into t_importcpdata (id,datafileid,day,cpid,appnumber,appversionnumber,channelid," +
						"goodsname,operator,source,province,fee,newusers,activeuser,payingcustomers,paidnumber) values ('"+model.getId()+"'," +
						"'"+model.getDatafileid()+"','"+model.getDay()+"','"+model.getCpid()+"'" +
						",'"+model.getAppnumber()+"','"+model.getAppversionnumber()+"','"+model.getChannelid()+"','"+
						model.getGoodsname()+"','"+model.getOperator()+"','"+
						model.getSource()+"','"+model.getProvince()+"','"+model.getFee()+
						"','"+model.getNewusers()+"','"+model.getActiveuser()+"','"+model.getPayingcustomers()+"','"+model.getPaidnumber()+"')";
			}
		}
		return ConnectionManager.getInstance().executeBatch(base, sql);
	}

	@Override
	public List<ImportData> queryImportDataByContent(String startdate,
			String enddate, int cpid, String appnumber,String appversionnumber, int channelid,String bd,
			String goodsname, String operator, String source, String province
			,String dimension,Admin admin) {
		String sql = "";
		{
			String gb = "";
			List<String> fl = new ArrayList<String>();
			
			if (!checkQueryDimension(dimension, "day"))
				gb += "0 ";
			else
				fl.add("day");
			gb += "day,";
			
			if (!checkQueryDimension(dimension, "appid"))
				gb += "'' ";
			else
				fl.add("appnumber");
			gb += "appnumber,";
			
			if (!checkQueryDimension(dimension, "appvid"))
				gb += "'' ";
			else
				fl.add("appversionnumber");
			gb += "appversionnumber,";
			
			if (!checkQueryDimension(dimension, "cpid"))
				gb += "-1 ";
			else
				fl.add("cpid");
			gb += "cpid,";
			
			if (!checkQueryDimension(dimension, "channelid"))
				gb += "-1 ";
			else
				fl.add("channelid");
			gb += "channelid,";
			
			if (!checkQueryDimension(dimension, "bd"))
				gb += "'' ";
			else
				fl.add("bd");
			gb += "bd,";
			
			if (!checkQueryDimension(dimension, "goodsname"))
				gb += "'' ";
			else
				fl.add("goodsname");
			gb += "goodsname,";
			
			if (!checkQueryDimension(dimension, "operator"))
				gb += "'' ";
			else
				fl.add("operator");
			gb += "operator,";
			
			if (!checkQueryDimension(dimension, "source"))
				gb += "'' ";
			else
				fl.add("source");
			gb += "source,";
									
			if (!checkQueryDimension(dimension, "province"))
				gb += "'' ";
			else
				fl.add("province");
			gb += "province,";
			
			sql = "select 0 id,0 datafileid," + gb + "fee,newusers,activeuser,payingcustomers,paidnumber " +
				"from (select " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") +
				",sum(fee) fee,sum(newusers) newusers,sum(activeuser) activeuser,sum(payingcustomers) payingcustomers,sum(paidnumber) paidnumber from t_importdata t1,t_channel t2 " +
				"where t1.channelid=t2.id and (day>=" + startdate.replace("-", "")  + " " +
				"and day<=" + enddate.replace("-", "") + ") ";
			
			if (cpid > 0)
				sql += " and cpid='" + cpid + "' ";
			 if (appnumber != null && !appnumber.equals(""))
					sql += "and appnumber='" + appnumber +"' ";
			 if (appversionnumber != null && !appversionnumber.equals(""))
					sql += "and appversionnumber='" + appversionnumber +"' ";
			if (channelid > 0)
				sql += " and channelid='" + channelid + "' ";
			
			if ( bd!= null && !bd.equals(""))
				sql += "and bd='" + bd +"' ";
			
			if (goodsname != null && !goodsname.equals(""))
				sql += "and goodsname='" + goodsname +"' ";
			
			if (operator != null && !operator.equals(""))
				sql += "and operator='" + operator +"' ";
			
			if (source != null && !source.equals(""))
				sql += "and source='" + source +"' ";
			
			if (province != null && !province.equals(""))
				sql += "and province='" + province +"' ";
			if(!"true".equals(admin.getIsAdmin())){
				sql+=" and channelid in (select channelid from t_adminchannel where adminid="+admin.getId()+")";
			}
			
			sql += "group by " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") + ") a " +
					"order by day,cpid,channelid,operator,province";
		}
		logger.info("[Sql] [method:queryImportDataByContent] ["+sql+"]");
		ModelGenerator<ImportData> dbcpmg = new ModelGenerator<ImportData>(ImportData.class);
		return dbcpmg.generateList(base, sql);
	}

	@Override
	public List<ImportChannelData> queryImportChannelDataByContent(
			String startdate, String enddate, int cpid, String appnumber,String appversionnumber,
			int channelid,String bd, String goodsname, String operator, String source,
			String province, String dimension,Admin admin) {
		String sql = "";
		{
			String gb = "";
			List<String> fl = new ArrayList<String>();
			
			if (!checkQueryDimension(dimension, "day"))
				gb += "0 ";
			else
				fl.add("day");
			gb += "day,";
			
			if (!checkQueryDimension(dimension, "appid"))
				gb += "'' ";
			else
				fl.add("appnumber");
			gb += "appnumber,";
			
			if (!checkQueryDimension(dimension, "appvid"))
				gb += "'' ";
			else
				fl.add("appversionnumber");
			gb += "appversionnumber,";
			
			if (!checkQueryDimension(dimension, "cpid"))
				gb += "-1 ";
			else
				fl.add("cpid");
			gb += "cpid,";
			
			if (!checkQueryDimension(dimension, "channelid"))
				gb += "-1 ";
			else
				fl.add("channelid");
			gb += "channelid,";
			
			if (!checkQueryDimension(dimension, "bd"))
				gb += "'' ";
			else
				fl.add("bd");
			gb += "bd,";
			
			if (!checkQueryDimension(dimension, "goodsname"))
				gb += "'' ";
			else
				fl.add("goodsname");
			gb += "goodsname,";
			
			if (!checkQueryDimension(dimension, "operator"))
				gb += "'' ";
			else
				fl.add("operator");
			gb += "operator,";
			
			if (!checkQueryDimension(dimension, "source"))
				gb += "'' ";
			else
				fl.add("source");
			gb += "source,";
			
			if (!checkQueryDimension(dimension, "province"))
				gb += "'' ";
			else
				fl.add("province");
			gb += "province,";
			
			sql = "select 0 id,0 datafileid," + gb + "fee,newusers,activeuser,payingcustomers,paidnumber " +
				"from (select " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") +
				",sum(fee) fee,sum(newusers) newusers,sum(activeuser) activeuser,sum(payingcustomers) payingcustomers,sum(paidnumber) paidnumber from t_importchanneldata t1,t_channel t2 " +
				"where t1.channelid=t2.id and (day>=" + startdate.replace("-", "")  + " " +
				"and day<=" + enddate.replace("-", "") + ") ";
			
			if (cpid > 0)
				sql += " and cpid='" + cpid + "' ";
			 if (appnumber != null && !appnumber.equals(""))
					sql += "and appnumber='" + appnumber +"' ";
			 if (appversionnumber != null && !appversionnumber.equals(""))
					sql += "and appversionnumber='" + appversionnumber +"' ";
			if (channelid > 0)
				sql += " and channelid='" + channelid + "' ";
			
			if (bd != null && !bd.equals(""))
				sql += "and bd='" + bd +"' ";
			
			if (goodsname != null && !goodsname.equals(""))
				sql += "and goodsname='" + goodsname +"' ";
			
			if (operator != null && !operator.equals(""))
				sql += "and operator='" + operator +"' ";
			
			if (source != null && !source.equals(""))
				sql += "and source='" + source +"' ";
			
			if (province != null && !province.equals(""))
				sql += "and province='" + province +"' ";
			if(!"true".equals(admin.getIsAdmin())){
				sql+=" and channelid in (select channelid from t_adminchannel where adminid="+admin.getId()+")";
			}
			
			sql += "group by " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") + ") a " +
					"order by day,channelid,operator,province";
		}
		logger.info("[SQL] [method:queryImportChannelDataByContent] [sql:"+sql+"]");
		ModelGenerator<ImportChannelData> dbcpmg = new ModelGenerator<ImportChannelData>(ImportChannelData.class);
		return dbcpmg.generateList(base, sql);
	}

	@Override
	public List<ImportCpData> queryImportCpDataByContent(String startdate,
			String enddate, int cpid, String appnumber, int channelid,String bd,
			String goodsname, String operator, String source, String province,
			String dimension) {
		String sql = "";
		{
			String gb = "";
			List<String> fl = new ArrayList<String>();
			
			if (!checkQueryDimension(dimension, "day"))
				gb += "0 ";
			else
				fl.add("day");
			gb += "day,";
			
			if (!checkQueryDimension(dimension, "appid"))
				gb += "'' ";
			else
				fl.add("appnumber");
			gb += "appnumber,";
			
			if (!checkQueryDimension(dimension, "cpid"))
				gb += "-1 ";
			else
				fl.add("cpid");
			gb += "cpid,";
			
			if (!checkQueryDimension(dimension, "channelid"))
				gb += "-1 ";
			else
				fl.add("channelid");
			gb += "channelid,";
			
			if (!checkQueryDimension(dimension, "bd"))
				gb += "'' ";
			else
				fl.add("bd");
			gb += "bd,";
			
			if (!checkQueryDimension(dimension, "goodsname"))
				gb += "'' ";
			else
				fl.add("goodsname");
			gb += "goodsname,";
			
			if (!checkQueryDimension(dimension, "operator"))
				gb += "'' ";
			else
				fl.add("operator");
			gb += "operator,";
			
			if (!checkQueryDimension(dimension, "source"))
				gb += "'' ";
			else
				fl.add("source");
			gb += "source,";
			
			if (!checkQueryDimension(dimension, "province"))
				gb += "'' ";
			else
				fl.add("province");
			gb += "province,";
			
			sql = "select 0 id,0 datafileid," + gb + "fee,newusers,activeuser,payingcustomers,paidnumber " +
				"from (select " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") +
				",sum(fee) fee,sum(newusers) newusers,sum(activeuser) activeuser,sum(payingcustomers) payingcustomers,sum(paidnumber) paidnumber from t_importcpdata t1,t_channel t2 " +
				"where t1.channelid=t2.id and (day>=" + startdate.replace("-", "")  + " " +
				"and day<=" + enddate.replace("-", "") + ") ";
			
			if (cpid > 0)
				sql += " and cpid='" + cpid + "' ";
			 if (appnumber != null && !appnumber.equals(""))
					sql += "and appnumber='" + appnumber +"' ";
			if (channelid > 0)
				sql += " and channelid='" + channelid + "' ";
			
			if (bd != null && !bd.equals(""))
				sql += "and bd='" + bd +"' ";
			
			if (goodsname != null && !goodsname.equals(""))
				sql += "and goodsname='" + goodsname +"' ";
			
			if (operator != null && !operator.equals(""))
				sql += "and operator='" + operator +"' ";
			
			if (source != null && !source.equals(""))
				sql += "and source='" + source +"' ";
			 
			if (province != null && !province.equals(""))
				sql += "and province='" + province +"' ";
			
			sql += "group by " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") + ") a " +
					"order by day,cpid,operator,province";
		}
		ModelGenerator<ImportCpData> dbcpmg = new ModelGenerator<ImportCpData>(ImportCpData.class);
		return dbcpmg.generateList(base, sql);
	}

	@Override
	public List<KpaySyncLog> getSyncDataAsImportData(String startday,
			String endday) {
		String sql = "select t.day day,t.appversionnumber appversionnumber,t.channelid channelid,t.chid chid,t.operatorname operatorname,t.provider provider,sum(orderamount) orderamount from t_kpaysynclog t where t.resultcode='100000' and t.day>="+startday+" and t.day<="+endday+" group by t.day,t.appversionnumber,t.channelid,t.chid,t.operatorname,t.provider order by t.provider desc";
		ModelGenerator<KpaySyncLog> dbcpmg = new ModelGenerator<KpaySyncLog>(KpaySyncLog.class);
		return dbcpmg.generateList(base, sql);
	}

}
 